package edu.cicese.alz;

import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import java.io.File;
import java.io.FilenameFilter;
import java.util.ArrayList;

/**
 * Created by: Eduardo Quintana Contreras
 * Date: 13/04/12
 * Time: 12:37 PM
 */
public class ExcelUtil {

	private static float[] alphas = new float[]{0,0.1f,0.2f,0.3f,0.4f,0.5f,0.6f,0.7f,0.8f,0.9f,1.0f};
	private static String workingPath;

	public static void getAverages(String path) {
		workingPath = path;
		File dir = new File(path);
		FilenameFilter filter = new FilenameFilter() {
			public boolean accept(File dir, String name) {
				return name.toLowerCase().endsWith(".xls");
			}
		};
		File[] files = dir.listFiles(filter);

		try {
			if (files != null) {

				ArrayList<ArrayList<ArrayList<Double>>> magnitudesPerUser = new ArrayList<>();
				ArrayList<ArrayList<Double>> magnitudesPerFile = new ArrayList<>();
				ArrayList<Double> magnitudesPerAlpha = new ArrayList<>();

				ArrayList<Double> frequenciesPerUser = new ArrayList<>();

				double[][] sumArray = initiateArray();
				double magnitude;
				int filesPerUser = 0;

				int userID = 0, tmpUserID;
				for (File file : files) {
					String filename = file.getName();
					tmpUserID = Integer.parseInt(filename.substring(4, 6));

					Workbook workbook = Workbook.getWorkbook(file);
					Sheet worksheet = workbook.getSheet(0);

					if (userID != tmpUserID) {
						if (userID != 0) {
							saveUser(userID, magnitudesPerUser, sumArray, filesPerUser, frequenciesPerUser);
						}
						userID = tmpUserID;
						sumArray = initiateArray();
						filesPerUser = 0;
						magnitudesPerUser.clear();
						frequenciesPerUser.clear();
					}
					filesPerUser++;

					magnitudesPerFile.clear();
					for (int r = 2; r <= 12; r++) {
						magnitudesPerAlpha.clear();
						for (int c = 1; c <= 5; c++) {
							magnitude = Double.parseDouble(worksheet.getCell(c, r).getContents());
							sumArray[r - 2][c - 1] += magnitude;
							magnitudesPerAlpha.add(magnitude);
						}
						magnitudesPerFile.add((ArrayList<Double>)magnitudesPerAlpha.clone());
					}
					magnitudesPerUser.add((ArrayList<ArrayList<Double>>)magnitudesPerFile.clone());

					frequenciesPerUser.add(Double.parseDouble(worksheet.getCell(7, 2).getContents()));

				}
				saveUser(userID, magnitudesPerUser, sumArray, filesPerUser, frequenciesPerUser);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static double[][] initiateArray() {
		double[][] emptyArray = new double[11][5];
		for (int r = 0; r <= 10; r++) {
			for (int c = 0; c <= 4; c++) {
				emptyArray[r][c] = 0;
			}
		}
		return emptyArray;
	}

	public static double[][] getMeans(double[][] sumArray, int filesPerUser) {
		double[][] meanArray = new double[11][5];
		for (int r = 0; r <= 10; r++) {
			for (int c = 0; c <= 4; c++) {
				meanArray[r][c] = sumArray[r][c] / filesPerUser;
			}
		}
		return meanArray;
	}

	public static double[][] getSDs(ArrayList<ArrayList<ArrayList<Double>>> magnitudesPerUser,
	                                double[][] meanArray, int filesPerUser) {
		double[][] sdArray = new double[11][5];
		double[][] sumArray = initiateArray();

		for (ArrayList<ArrayList<Double>> mPerFile : magnitudesPerUser) {
			for (int r = 0; r <= 10; r++) {
				for (int c = 0; c <= 4; c++) {
					sumArray[r][c] += Math.pow(mPerFile.get(r).get(c) - meanArray[r][c], 2);
				}
			}
		}

		for (int r = 0; r <= 10; r++) {
			for (int c = 0; c <= 4; c++) {
				sdArray[r][c] = Math.sqrt(sumArray[r][c] / (filesPerUser - 1));
			}
		}

		return sdArray;
	}

	public static void saveUser(int userID, ArrayList<ArrayList<ArrayList<Double>>> magnitudesPerUser,
	                                double[][] sumArray, int filesPerUser, ArrayList<Double> frequenciesPerUser) {
		double[][] meanArray = getMeans(sumArray, filesPerUser);
		double[][] sdArray = getSDs(magnitudesPerUser, meanArray, filesPerUser);
		String folder = workingPath + "summary\\";
		new File(folder).mkdirs();
		String user = userID < 10 ? "0" + userID : userID + "";
		String filename = "user" + user;
		String file = folder + filename + ".xls";
		try {
			WorkbookSettings wbSetting = new WorkbookSettings();
			wbSetting.setUseTemporaryFileDuringWrite(true);
			WritableWorkbook workbook = Workbook.createWorkbook(new File(file), wbSetting);

			WritableFont fontNormal = new WritableFont(WritableFont.createFont("CALIBRI"), 7);
			WritableFont fontBold = new WritableFont(WritableFont.createFont("CALIBRI"), 12, WritableFont.BOLD);

			WritableCellFormat formatNormal = new WritableCellFormat(fontNormal);
			WritableCellFormat formatNormalDecimalAlpha = new WritableCellFormat(fontBold, new NumberFormat("0.0"));
			WritableCellFormat formatNormalDecimal = new WritableCellFormat(fontNormal, new NumberFormat("0.00"));
			WritableCellFormat formatBoldDecimal = new WritableCellFormat(fontBold, new NumberFormat("0.00"));
			WritableCellFormat formatDivisor = new WritableCellFormat();
			formatDivisor.setBackground(Colour.GREY_25_PERCENT);
			WritableCellFormat formatBoldCenter = new WritableCellFormat(fontBold);
			formatBoldCenter.setAlignment(Alignment.CENTRE);

			WritableSheet worksheet = workbook.createSheet(filename, 0);
			worksheet.addCell(new Label(0, 1, "Alpha", formatBoldCenter));
			worksheet.addCell(new Label(1, 1, "Magnitude", formatBoldCenter));
			worksheet.addCell(new Label(2, 1, "Magnitude [r]", formatBoldCenter));
			worksheet.addCell(new Label(3, 1, "Magnitude [-g]", formatBoldCenter));
			worksheet.addCell(new Label(4, 1, "Magnitude [-g][r]", formatBoldCenter));
			worksheet.addCell(new Label(5, 1, "Magnitude [-g][r][min]", formatBoldCenter));
			worksheet.addCell(new Label(7, 1, "Frequencies", formatBoldCenter));

			for (int r = 0; r <= 10; r++) {
				worksheet.addCell(new Number(0, r + 2, alphas[r], formatNormalDecimalAlpha));
				for (int c = 0; c <= 3; c++) {
					worksheet.addCell(new Number(c + 1, r + 2, meanArray[r][c], formatNormalDecimal));
				}
				worksheet.addCell(new Number(5, r + 2, meanArray[r][4], formatBoldDecimal));
			}

			worksheet.addCell(new Label(0, 16, "Alpha", formatBoldCenter));
			worksheet.addCell(new Label(1, 16, "Magnitude", formatBoldCenter));
			worksheet.addCell(new Label(2, 16, "Magnitude [r]", formatBoldCenter));
			worksheet.addCell(new Label(3, 16, "Magnitude [-g]", formatBoldCenter));
			worksheet.addCell(new Label(4, 16, "Magnitude [-g][r]", formatBoldCenter));
			worksheet.addCell(new Label(5, 16, "Magnitude [-g][r][min]", formatBoldCenter));

			for (int r = 0; r <= 10; r++) {
				worksheet.addCell(new Number(0, r + 17, alphas[r], formatNormalDecimalAlpha));
				for (int c = 0; c <= 3; c++) {
					worksheet.addCell(new Number(c + 1, r + 17, sdArray[r][c], formatNormalDecimal));
				}
				worksheet.addCell(new Number(5, r + 17, sdArray[r][4], formatBoldDecimal));
			}

			for (int r = 0; r <= frequenciesPerUser.size() - 1; r++) {
				worksheet.addCell(new Number(7, r + 2, frequenciesPerUser.get(r), formatBoldDecimal));
			}

			int[] summaryColumnWidths = new int[]{7, 14, 15, 16, 18, 22, 2, 13};

			for (int c = 0; c < summaryColumnWidths.length; c++) {
				worksheet.setColumnView(c, summaryColumnWidths[c]);
			}

			workbook.write();
			workbook.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void joinUsers(String path) {
		File dir = new File(path);
		FilenameFilter filter = new FilenameFilter() {
			public boolean accept(File dir, String name) {
				return name.toLowerCase().endsWith(".xls");
			}
		};
		File[] files = dir.listFiles(filter);

		try {
			if (files != null) {
//				String folder = "D:\\_proyectoAlz\\analysis\\accelerometer\\walking\\summary\\";
				new File(path + "globalSummary").mkdirs();
				WorkbookSettings wbSetting = new WorkbookSettings();
				wbSetting.setUseTemporaryFileDuringWrite(true);
				WritableWorkbook newWorkbook = Workbook.createWorkbook(new File(path + "globalSummary\\globalSummary.xls"), wbSetting);

				WritableFont fontNormal = new WritableFont(WritableFont.createFont("Calibri"), 10);
				WritableFont fontBold = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD);

				WritableCellFormat formatNormal = new WritableCellFormat(fontNormal);
				WritableCellFormat formatNormalDecimalAlpha = new WritableCellFormat(fontBold, new NumberFormat("0.0"));
				WritableCellFormat formatNormalDecimal = new WritableCellFormat(fontNormal, new NumberFormat("0.00"));
				WritableCellFormat formatBoldDecimal = new WritableCellFormat(fontBold, new NumberFormat("0.00"));
				WritableCellFormat formatDivisor = new WritableCellFormat();
				formatDivisor.setBackground(Colour.GREY_25_PERCENT);
				WritableCellFormat formatBoldCenter = new WritableCellFormat(fontBold);
				formatBoldCenter.setAlignment(Alignment.CENTRE);

				//Alphas
				WritableSheet newWorksheet = newWorkbook.createSheet("summary", 0);
				newWorksheet.addCell(new Label(0, 2, "Alpha", formatBoldCenter));
				for (int r = 3; r <= 13; r++) {
					newWorksheet.addCell(new Number(0, r, alphas[r - 3], formatNormalDecimalAlpha));
				}

				//Magnitude legend
				int users = files.length;
				newWorksheet.mergeCells(1, 0, users * 2, 0);
				newWorksheet.addCell(new Label(1, 0, "Magnitude [-g][r][min]", formatBoldCenter));

				//User legends
				for (int user = 1, c = 1; user <= users; user++, c++) {
					newWorksheet.mergeCells(c, 1, c + 1, 1);
					newWorksheet.addCell(new Label(c, 2, "Mean", formatBoldCenter));
					c++;
					newWorksheet.addCell(new Label(c, 2, "SD", formatBoldCenter));
				}

				int c = 1;
				int sdRow, newRow;
				for (File file : files) {
					Workbook workbook = Workbook.getWorkbook(file);
					Sheet worksheet = workbook.getSheet(0);

					newWorksheet.addCell(new Label(c, 1, "User " + file.getName().substring(4, 6), formatBoldCenter));

					for (int meanRow = 2; meanRow <= 12; meanRow++) {
						sdRow = meanRow + 15;
						newRow = meanRow + 1;
						newWorksheet.addCell(new Number(c, newRow, Double.parseDouble(worksheet.getCell(5, meanRow).getContents()), formatNormalDecimal));
						newWorksheet.addCell(new Number(c + 1, newRow, Double.parseDouble(worksheet.getCell(5, sdRow).getContents()), formatNormalDecimal));
					}
					c += 2;
				}
				newWorkbook.write();
				newWorkbook.close();
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
}
